﻿//创建时间: 2010-10-11
//创建作者: 李晓忠
//功能说明: CMS系统 结点信息
//最后修改: 2010-10-13 李晓忠 添加删除结点和子结点功能  
//最后修改: 2010-10-14 李晓忠 添加结点树  
//最后修改: 2010-10-28 李晓忠 移动结点 MoveNodeToNewParentId  
using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;
using System.Data;
using TRPOP.Common;
using TRPOP.Config;
using TRPOP.CMS.Model;
using TRPOP.CMS.DALFactory;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace TRPOP.CMS.DALSQLServer
{
    public class NodeDAL : INodeDAL
    {

        #region 保存 Save
        public EnumSubmitResult Save(NodeInfo info)
        {
            string strStoreProcedure = SystemInfoHelper.getSystemInfo().DBprefixTableName + "_" + "Node_Save";
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetStoredProcCommand(strStoreProcedure);

            odbDataBase.AddInParameter(odbCommand, "@Editor", System.Data.DbType.String, info.Editor);
            odbDataBase.AddInParameter(odbCommand, "@Pager", System.Data.DbType.String, info.Pager);
            odbDataBase.AddInParameter(odbCommand, "@IndexName", System.Data.DbType.String, info.IndexName);
            odbDataBase.AddInParameter(odbCommand, "@IndexPortalURL", System.Data.DbType.String, info.IndexPortalURL);
            odbDataBase.AddInParameter(odbCommand, "@ContentPortalURL", System.Data.DbType.String, info.ContentPortalURL);
            odbDataBase.AddInParameter(odbCommand, "@InheritNodeID", System.Data.DbType.Int32, info.InheritNodeID);
            odbDataBase.AddInParameter(odbCommand, "@ParentId", System.Data.DbType.Int32, info.ParentId);
            odbDataBase.AddInParameter(odbCommand, "@ModelId", System.Data.DbType.Int32, info.ModelId);
            odbDataBase.AddInParameter(odbCommand, "@Name", System.Data.DbType.String, info.Name);
            odbDataBase.AddInParameter(odbCommand, "@KeyWord", System.Data.DbType.String, info.KeyWord);
            odbDataBase.AddInParameter(odbCommand, "@Url", System.Data.DbType.String, info.Url);
            odbDataBase.AddInParameter(odbCommand, "@NodeTypeId", System.Data.DbType.Int32, info.NodeTypeId);
            odbDataBase.AddInParameter(odbCommand, "@ContentPubTypeId", System.Data.DbType.Int32, info.ContentPubTypeId);
            odbDataBase.AddInParameter(odbCommand, "@IsAuoPub", System.Data.DbType.Boolean, info.IsAuoPub);
            odbDataBase.AddInParameter(odbCommand, "@IsContribute", System.Data.DbType.Boolean, info.IsContribute);
            odbDataBase.AddInParameter(odbCommand, "@IsHidde", System.Data.DbType.Boolean, info.IsHidde);
            odbDataBase.AddInParameter(odbCommand, "@DisplayOrder", System.Data.DbType.Int32, info.DisplayOrder);
            odbDataBase.AddInParameter(odbCommand, "@Detail", System.Data.DbType.String, info.Detail);
            odbDataBase.AddInParameter(odbCommand, "@IndexTemplate", System.Data.DbType.String, info.IndexTemplate);
            odbDataBase.AddInParameter(odbCommand, "@ImageTemplate", System.Data.DbType.String, info.ImageTemplate);
            odbDataBase.AddInParameter(odbCommand, "@DetailTemplate", System.Data.DbType.String, info.DetailTemplate);
            odbDataBase.AddInParameter(odbCommand, "@SourcePSN", System.Data.DbType.String, info.SourcePSN);
            odbDataBase.AddInParameter(odbCommand, "@SourceUrl", System.Data.DbType.String, info.SourceUrl);
            odbDataBase.AddInParameter(odbCommand, "@SourceRootReg", System.Data.DbType.String, info.SourceRootReg);
            odbDataBase.AddInParameter(odbCommand, "@HtmlPSN", System.Data.DbType.String, info.HtmlPSN);
            odbDataBase.AddInParameter(odbCommand, "@HtmlUrl", System.Data.DbType.String, info.HtmlUrl);
            odbDataBase.AddInParameter(odbCommand, "@HtmlRootReg", System.Data.DbType.String, info.HtmlRootReg);
            odbDataBase.AddInParameter(odbCommand, "@HtmlNameReg", System.Data.DbType.String, info.HtmlNameReg);
            odbDataBase.AddInParameter(odbCommand, "@SEOTitle", System.Data.DbType.String, info.SEOTitle);
            odbDataBase.AddInParameter(odbCommand, "@SEOKey", System.Data.DbType.String, info.SEOKey);
            odbDataBase.AddInParameter(odbCommand, "@SEODescription", System.Data.DbType.String, info.SEODescription);
            odbDataBase.AddInParameter(odbCommand, "@ReadLimit", System.Data.DbType.Int32, info.ReadLimit);
            odbDataBase.AddInParameter(odbCommand, "@AddTime", System.Data.DbType.DateTime, info.AddTime);
            odbDataBase.AddInParameter(odbCommand, "@AddUserId", System.Data.DbType.Int32, info.AddUserId);
            odbDataBase.AddInParameter(odbCommand, "@UpdateUserId", System.Data.DbType.Int32, info.UpdateUserId);
            odbDataBase.AddInParameter(odbCommand, "@UpdateTime", System.Data.DbType.DateTime, info.UpdateTime);
            odbDataBase.AddInParameter(odbCommand, "@WaterPic", System.Data.DbType.String, info.WaterPic);
            odbDataBase.AddInParameter(odbCommand, "@IsWater", System.Data.DbType.Boolean, info.IsWater);
            odbDataBase.AddInParameter(odbCommand, "@Extcredits1", System.Data.DbType.Decimal, info.Extcredits1);
            odbDataBase.AddInParameter(odbCommand, "@Extcredits2", System.Data.DbType.Decimal, info.Extcredits2);
            odbDataBase.AddInParameter(odbCommand, "@Extcredits3", System.Data.DbType.String, info.Extcredits3);
            odbDataBase.AddInParameter(odbCommand, "@Extcredits4", System.Data.DbType.String, info.Extcredits4);
            odbDataBase.AddInParameter(odbCommand, "@Extcredits5", System.Data.DbType.String, info.Extcredits5);
            odbDataBase.AddInParameter(odbCommand, "@ParentPath", System.Data.DbType.String, info.ParentPath);
            odbDataBase.AddInParameter(odbCommand, "@ArrChildId", System.Data.DbType.String, info.ArrChildId);
            odbDataBase.AddParameter(odbCommand, "@NodeId", System.Data.DbType.Int32, ParameterDirection.InputOutput, null, DataRowVersion.Default, info.NodeId);

            EnumSubmitResult enmResult = EnumSubmitResult.Failed;
            try
            {
                odbDataBase.ExecuteNonQuery(odbCommand);
                info.NodeId = (int)odbDataBase.GetParameterValue(odbCommand, "@NodeId");
                enmResult = EnumSubmitResult.Success;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return enmResult;
        }

        #endregion


        #region 获取实体 GetInfoById
        public NodeInfo GetInfoById(int id)
        {
            string strSqlString = "SELECT * FROM " + SystemInfoHelper.getSystemInfo().DBprefixTableName + "_node_Node WHERE NodeId=" + id;
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);
            NodeInfo oInfo = null;
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    if (reader.Read())
                    {
                        oInfo = new NodeInfo();
                        ModelHelper.LoadInfoData(oInfo, reader);
                    }
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return oInfo;
        }
        #endregion

        #region 删除 DeleteInfoById
        public EnumSubmitResult DeleteInfoById(int id)
        {
            return DeleteInfoByIds(id.ToString());
        }
        #endregion

        #region 删除 DeleteInfoByIds
        public EnumSubmitResult DeleteInfoByIds(string ids)
        {
            string strSqlString = "DELETE FROM " + SystemInfoHelper.getSystemInfo().DBprefixTableName + "_node_Node WHERE NodeId in(" + ids + ")";
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);

            EnumSubmitResult enmResult = EnumSubmitResult.Failed;
            try
            {
                odbDataBase.ExecuteNonQuery(odbCommand);
                enmResult = EnumSubmitResult.Success;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return enmResult;
        }
        #endregion

        #region 逻辑删除结点和子结点 DeleteNodeAndChildrenById
        public EnumSubmitResult DeleteNodeAndChildrenById(int id)
        {
            string strperfixDB = SystemInfoHelper.getSystemInfo().DBprefixTableName;
            string strSqlString = "WITH nodelist(NodeId)  as  "
                                + "(  SELECT NodeId FROM  " + strperfixDB + "_node_Node WHERE NodeId= " + id
                                + "UNION ALL      SELECT A.NodeId  FROM  " + strperfixDB + "_node_Node A,nodelist b where a.ParentId = b.NodeId )  "
                                 + "UPDATE " + strperfixDB + "_node_Node SET IsHidde=1  where NodeId in(  SELECT NodeId  from nodelist)";
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);

            EnumSubmitResult enmResult = EnumSubmitResult.Failed;
            try
            {
                odbDataBase.ExecuteNonQuery(odbCommand);
                enmResult = EnumSubmitResult.Success;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return enmResult;
        }
        #endregion

        #region 自定义列表（无分页） GetListByCondition
        public List<NodeInfo> GetListByCondition(int topn, string condition, string orderby)
        {
            string strSqlString = "SELECT ";
            if (topn > 0)
            {
                strSqlString += " TOP " + topn;
            }
            strSqlString += " * FROM " + SystemInfoHelper.getSystemInfo().DBprefixTableName + "_node_Node WHERE 1=1"; if (condition != "")
            {
                strSqlString += condition;
            }
            if (orderby != "")
            {
                strSqlString += " ORDER BY " + orderby;
            }
            else
            {
                strSqlString += " ORDER BY NodeId DESC";
            }

            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);
            List<NodeInfo> olstInfo = new List<NodeInfo>(); NodeInfo oInfo = null;
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    while (reader.Read())
                    {
                        oInfo = new NodeInfo();
                        ModelHelper.LoadInfoData(oInfo, reader);
                        olstInfo.Add(oInfo);
                    }
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return olstInfo;
        }
        #endregion
       

        #region 自定义列表（分页） GetListByPager
        public List<NodeInfo> GetListByPager(int pageNumber, int pageSize, string condition, string orderby, out int recordCount)
        {
            string strStoreProcedure = SystemInfoHelper.getSystemInfo().PagerStoreProcedureName;
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetStoredProcCommand(strStoreProcedure);
            List<NodeInfo> olstInfo = new List<NodeInfo>(); NodeInfo oInfo = null;
            odbDataBase.AddInParameter(odbCommand, "@TableName", System.Data.DbType.String, SystemInfoHelper.getSystemInfo().DBprefixTableName + "_node_Node");
            odbDataBase.AddInParameter(odbCommand, "@ShowColumns", System.Data.DbType.String, "*");
            odbDataBase.AddInParameter(odbCommand, "@pageNumber", System.Data.DbType.Int32, pageNumber);
            odbDataBase.AddInParameter(odbCommand, "@pageSize", System.Data.DbType.Int32, pageSize);
            odbDataBase.AddInParameter(odbCommand, "@condition", System.Data.DbType.String, condition);
            odbDataBase.AddInParameter(odbCommand, "@orderby", System.Data.DbType.String, orderby);
            odbDataBase.AddOutParameter(odbCommand, "@recordCount", System.Data.DbType.Int32, 0);
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    while (reader.Read())
                    {
                        oInfo = new NodeInfo();
                        ModelHelper.LoadInfoData(oInfo, reader);
                        olstInfo.Add(oInfo);
                    }
                }
                recordCount = int.Parse(odbCommand.Parameters["@RecordCount"].Value.ToString());
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return olstInfo;
        }
        #endregion

        #region 结点 树形结构 GetNodeListTree
        /// <summary>
        /// 结点树形结构
       /// </summary>
       /// <param name="ParentId"></param>
       /// <param name="level"></param>
       /// <param name="Condition"></param>
       /// <returns></returns>
        public List<NodeInfoWithLevel> GetNodeListTree(int ParentId, int level, string Condition)
        {
            string strStoreProcedure = SystemInfoHelper.getSystemInfo().DBprefixTableName + "_Node_GetListTree";
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetStoredProcCommand(strStoreProcedure);
            List<NodeInfoWithLevel> olstInfo = new List<NodeInfoWithLevel>();
            NodeInfoWithLevel oInfo = null;
            odbDataBase.AddInParameter(odbCommand, "@ParentId", System.Data.DbType.Int32, ParentId);
            odbDataBase.AddInParameter(odbCommand, "@level", System.Data.DbType.Int32, level);
            odbDataBase.AddInParameter(odbCommand, "@Condition", System.Data.DbType.String, Condition);
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    while (reader.Read())
                    {
                        oInfo = new NodeInfoWithLevel();
                        ModelHelper.LoadInfoData(oInfo, reader);
                        olstInfo.Add(oInfo);
                    }
                    while (reader.NextResult())
                    {
                        while (reader.Read())
                        {
                            oInfo = new NodeInfoWithLevel();
                            ModelHelper.LoadInfoData(oInfo, reader);
                            olstInfo.Add(oInfo);
                        }
                    }
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return olstInfo;
        }
        #endregion 

        #region 移动结点 MoveNodeToNewParentId
        
        public EnumSubmitResult MoveNodeToNewParentId(int nodeid,int parentId)
        {
            string strperfixDB = SystemInfoHelper.getSystemInfo().DBprefixTableName;
            string strSqlString = "UPDATE  " + strperfixDB + "_node_Node SET ParentId=" + parentId + " WHERE NodeId= " + nodeid;                             
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);

            EnumSubmitResult enmResult = EnumSubmitResult.Failed;
            try
            {
                odbDataBase.ExecuteNonQuery(odbCommand);
                enmResult = EnumSubmitResult.Success;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return enmResult;
        }
        #endregion

        #region 获取结点下所有子结点
        /// <summary>
        /// 获取结点下所有子结点(保含子结点的子结点)
        /// </summary>
        /// <param name="ParentID"></param>
        /// <returns></returns>
        public List<NodeInfo> GetNodeListByParentID(int ParentID)
        {
            string strperfixDB = SystemInfoHelper.getSystemInfo().DBprefixTableName;
            string strSqlString = "WITH nodelist(NodeId)  as  "
                                + "(  SELECT NodeId FROM  " + strperfixDB + "_node_Node WHERE [ParentId]= " + ParentID
                                + " UNION ALL      SELECT A.NodeId  FROM  " + strperfixDB + "_node_Node A,nodelist b where a.ParentId = b.NodeId )  "
                                 + "SELECT * FROM  " + strperfixDB + "_node_Node  where NodeId in(  SELECT NodeId  from nodelist)";
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);
            List<NodeInfo> olstInfo = new List<NodeInfo>(); NodeInfo oInfo = null;
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    while (reader.Read())
                    {
                        oInfo = new NodeInfo();
                        ModelHelper.LoadInfoData(oInfo, reader);
                        olstInfo.Add(oInfo);
                    }
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return olstInfo;
        }
        #endregion

        #region 更新附件表
        /// <summary>
        /// 更新附件表的地址
        /// </summary>
        /// <param name="strNodeID"></param>
        /// <param name="strOld"></param>
        /// <param name="strNew"></param>
        /// <returns></returns>
        public EnumSubmitResult ResetAllPSN(string strNodeID, string strOld, string strNew)
        {
            string strperfixDB = SystemInfoHelper.getSystemInfo().DBprefixTableName;
            string strSqlString =
               string.Format("UPDATE  {0}_content_ContentUpFile SET [FileRoot] = REPLACE([FileRoot],'{1}','{2}') WHERE NodeId in ({3}) ",
                               strperfixDB, strOld, strNew, strNodeID);
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);

            EnumSubmitResult enmResult = EnumSubmitResult.Failed;
            try
            {
                odbDataBase.ExecuteNonQuery(odbCommand);
                enmResult = EnumSubmitResult.Success;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return enmResult;
        }
        #endregion





    }
}
